use master
go

create database Utest
go

use UTest 
go

create table StuInfo
(
	StuID int primary key identity(1,1),
	StuName varchar(10) not null,
	StuAge int,
	StuSex char(1) default(0) check(StuSex in(0, 1)),
	Time datetime
)

insert into 
	StuInfo(StuName, StuAge, StuSex, Time)
values
	('Tom',19,1,NUll),
	('Jeck',20,0,NUll),
	('Rose',21,1,NUll),
	('Lulu',19,1,NUll),
	('Lili',21,0,NUll),
	('abc',20,1,'2007-01-07 01:11:36.590')
go

-- courseInfo scoreInfo
create table CourseInfo
(
	CourseID int primary key identity(1,1),
	CourseName varchar(15) not null,
	CourseMarks int
)

insert into	
	CourseInfo(CourseName, CourseMarks)
values
	('JavaBase',4),
	('HTML',2),
	('JavaScript',2),
	('SqlBase',2)
go

create table ScoreInfo
(
	ScoreId int primary key identity(1,1),
	StuId int foreign key references StuInfo(StuID),
	CourseID int foreign key references CourseInfo(CourseID) not null,
	Score int
)

insert into
	ScoreInfo(StuID,CourseId,Score)
values
	(1,1,80),
	(1,2,85),
	(1,4,50),
	(2,1,75),
	(2,3,45),
	(2,4,75),
	(3,1,45),
	(4,1,95),
	(4,2,75),
	(4,3,90),
	(4,4,45)
go

select * from StuInfo
select * from CourseInfo
select * from ScoreInfo

-- 1.查询出每个学生所选修的课程的数量和所选修的课程的考试的平均分
select
	Sti.StuName,count(Sci.CourseID),avg(Sci.Score)
from
	StuInfo	Sti
join
	ScoreInfo Sci
on
	Sti.StuID = Sci.StuID
group by 
	Sci.StuID

-- 2.查询出每门课程的选修的学生的个数和学生考试的总分
select
	Ci.CoureName, count(Sci.CourseId), Sti.StuName, sum(Sci.Score)	
from
	CourseInfo Ci
join
	ScoreInfo	Sci
on
	Sci.CourseID = Ci.CourseID	
join
	StuInfo Sti
on
	Sti.StuID = Sci.StuID
group by
	Sci.StuID
order by
	Sci.Score desc

-- 3.查询出性别一样并且年龄一样的学生的信息
select
	S1.*
from
	StuInfo s1
join
	StuInfo s2
on
	S1.stuID <> S2.stuID and S1.stuSex = S2.stuSex and S1.stuAge = S2.stuAge

-- 4.查询出学分一样的课程信息
select
	*
from
	CourseInfo Ci
join
	CourseInfo Ci2
on
	Ci.CourseMarks = Ci2.CourseMarks


-- 5.查询出参加了考试的学生的学号，姓名，课程号和分数
select
	Sti.StuID,Sti.StuName,Sci.CourseID,Sci.Score
from
	StuInfo Sti
join
	ScoreInfo Sci
on
	Sti.stuID = Sci.StuID

-- 6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数
select
	Sti.StuID,Sti.StuName,Ci.*,Sci.Score
from
	StuInfo Sti
join
	ScoreInfo Sci
on
	Sti.stuID = Sci.StuID
join
	CourseInfo Ci
on
	Sci.CourseID = Ci.CourseID

-- 7.查询出没有参加考试的学生的学号和姓名
select
	Sti.StuID,Sti.StuName
from
	StuInfo Sti
join
	ScoreInfo Sci
on
	Sti.StuID not in (Sci.StuID )

-- 8.查询出是周六周天来报到的学生
select
	Sti.StuName
from
	StuInfo Sti
where
	datepart(weekday,getdate()) > 5

-- 9.查询出姓名中有字母a的学生的信息
select
	*
from
	StuInfo
where
	StuName like ('%a%')
-- 10.查询出选修了2门课程以上的并且考试平均分在70以上的学生的学号和考试平均分以及选修课程的数量
select
	StuID,count(Sci.CoureID),avg(Sci.Score)
from
	ScoreInfo Sci
group by
	Sci.StuID
having
	count(Sci.CoureID) > 2 and avg(Sci.Score) > 70
